package com.project.frba.bi.loading;

public class SchemaBuilder {
	
	private String statement;
	private String schemaName;
	private String supportTables;
			
	public SchemaBuilder(String schemaName){
		this.schemaName=schemaName;
	}
	
	public String getCreateSchemaStatement(){
		if(statement == null){
			buildCreateSchemaStatement();
		} 
		return this.statement;
	}
	
	public String getSupportTablesStatement(){
		if(supportTables == null){
			buildSupportTables();
		}
		return this.supportTables;
	}
	
	public String getFullCreationStatement(){
		return this.getCreateSchemaStatement() + " " + this.getSupportTablesStatement();
	}
	
	private void buildCreateSchemaStatement(){
		StringBuffer statement = new StringBuffer("");
		statement.append("CREATE DATABASE IF NOT EXISTS `" + schemaName + "`; USE `" + schemaName + "`;");
		this.statement= statement.toString().trim();
	}
	
	private void buildSupportTables(){
		StringBuffer buf = new StringBuffer();
		buf.append(buildPrivilegesTables());
		buf.append("  ");
		buf.append(buildUserTables());
		buf.append("  ");
		buf.append(buildDashboardDualTable());
		buf.append("  ");
		buf.append(buildDashboardQuadTable());
		this.supportTables = buf.toString();
	}
	
	private String buildPrivilegesTables(){
		return "DROP TABLE IF EXISTS `privileges`; " +
				"CREATE TABLE `privileges` (" +
				"`id` bigint(20) unsigned NOT NULL auto_increment, " +
				"`name` varchar(45) NOT NULL, " +
				"PRIMARY KEY  (`id`)" +
				") ENGINE=InnoDB DEFAULT CHARSET=utf8;" +
				"INSERT INTO `privileges` (`id`,`name`) VALUES" +
				" (1,'admin')," +
				" (2,'general'), " +
				"(3,'restringido');";
	}
	
	private String buildUserTables(){
		return "DROP TABLE IF EXISTS `users`; " +
				"CREATE TABLE `users` (  " +
				"`id` bigint(20) unsigned NOT NULL auto_increment,  " +
				"`username` varchar(45) NOT NULL,  " +
				"`password` varchar(45) NOT NULL,  " +
				"`privilege` bigint(20) unsigned NOT NULL,  " +
				"PRIMARY KEY  (`id`),  KEY `Index_2` USING BTREE (`privilege`),  " +
				"CONSTRAINT `privilege` FOREIGN KEY (`privilege`) " +
				"REFERENCES `privileges` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;" + 
				"INSERT INTO `users` (`username`,`password`,`privilege`) VALUES " + 
				"('admin', 'admin', 1);";
	}
	
	private String buildDashboardDualTable(){
		return 	"DROP TABLE IF EXISTS `dual_dashboard`; " +
				"CREATE TABLE `dual_dashboard` (  " +
				"`id` bigint(20) unsigned NOT NULL auto_increment,  " +
				"`name` varchar(255) NOT NULL,  " +
				"`user` bigint(20) unsigned NOT NULL,  " +
				"`first` varchar(4096) default NULL,  " +
				"`second` varchar(4096) default NULL,  " +
				"PRIMARY KEY  (`id`),  " +
				"UNIQUE KEY `nameuser` (`user`,`name`),  " +
				"KEY `user` USING BTREE (`user`),  " +
				"CONSTRAINT `FK_dual_dashboard_1` FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) " +
				"ENGINE=InnoDB DEFAULT CHARSET=latin1;";
	}
	
	private String buildDashboardQuadTable(){
		return 	"DROP TABLE IF EXISTS `quad_dashboard`; " +
				"CREATE TABLE `quad_dashboard` (  " +
				"`id` bigint(20) unsigned NOT NULL auto_increment,  " +
				"`name` varchar(255) NOT NULL,  " +
				"`user` bigint(20) unsigned NOT NULL,  " +
				"`first` varchar(4096) default NULL,  " +
				"`second` varchar(4096) default NULL,  " +
				"`third` varchar(4096) default NULL,  " +
				"`fourth` varchar(4096) default NULL,  " +
				"PRIMARY KEY  (`id`),  " +
				"UNIQUE KEY `nameuser` (`user`,`name`),  " +
				"KEY `user` USING BTREE (`user`),  " +
				"CONSTRAINT `FK_quad_dashboard_1` FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) " +
				"ENGINE=InnoDB DEFAULT CHARSET=latin1;";
	}
	
	
	
}